﻿Imports DTO
Imports System.Data.Linq

Public Class SachDao
    Public Shared Function LayBang() As Table(Of Sach)
        Dim db As New ThuVienDataContext()
        Return db.Saches
    End Function

    Public Shared Function LayBang_Tracuu() As Object
        Dim db As New ThuVienDataContext()
        Dim kq = From s In db.Saches _
                 From k In db.Khos _
                 From l In db.TheLoais _
                 Where k.MS = s.MS And l.MTL = s.MTL And k.Tong_so > 0 _
                 Select s.Tua_sach, s.Tac_gia, s.MS, l.Ten_the_loai, k.Ton_kho
        Return kq
    End Function

    Public Shared Function LayBang_Tracuu_Theotuasach(ByVal tua As String) As Object
        Dim db As New ThuVienDataContext()
        Dim kq = From s In db.Saches _
                 From k In db.Khos _
                 From l In db.TheLoais _
                 Where s.Tua_sach.Contains(tua) And k.MS = s.MS And l.MTL = s.MTL And k.Tong_so > 0 _
                 Select s.Tua_sach, s.Tac_gia, s.MS, l.Ten_the_loai, k.Ton_kho
        Return kq
    End Function

    Public Shared Function LayBang_Tracuu_Theotacgia(ByVal tg As String) As Object
        Dim db As New ThuVienDataContext()
        Dim kq = From s In db.Saches _
                 From k In db.Khos _
                 From l In db.TheLoais _
                 Where s.Tac_gia.Contains(tg) And k.MS = s.MS And l.MTL = s.MTL And k.Tong_so > 0 _
                 Select s.Tua_sach, s.Tac_gia, s.MS, l.Ten_the_loai, k.Ton_kho
        Return kq
    End Function

    Public Shared Function LayBang_Tracuu_Theomasach(ByVal m As Integer) As Object
        Dim db As New ThuVienDataContext()
        Dim kq = From s In db.Saches _
                 From k In db.Khos _
                 From l In db.TheLoais _
                 Where k.MS = m And k.MS = s.MS And l.MTL = s.MTL And k.Tong_so > 0 _
                 Select s.Tua_sach, s.Tac_gia, s.MS, l.Ten_the_loai, k.Ton_kho
        Return kq
    End Function

    Public Shared Function LayDanhSach() As IEnumerable(Of Sach)
        Dim lst As IEnumerable(Of Sach)
        Dim db As New ThuVienDataContext()
        lst = From s In db.Saches Select s
        Return lst
    End Function

    Public Shared Function LaySach(ByVal m As Integer) As Sach
        Dim db As New ThuVienDataContext()
        Dim lst = From s In db.Saches Where s.MS = m Select s
        Dim sch As New Sach

        If lst.Count > 0 Then
            sch = lst.Single
        Else
            sch.MS = -1
        End If

        Return sch
    End Function

    Public Shared Sub Them(ByVal s As Sach)
        Dim db As New ThuVienDataContext()
        db.Saches.InsertOnSubmit(s)
    End Sub

    Public Shared Sub ThemSach(ByVal s As Sach, ByVal nc As Kho)
        Dim db As New ThuVienDataContext()
        db.Saches.InsertOnSubmit(s)
        db.SubmitChanges()
        nc.MS = s.MS
        nc.Noi_luu = "Auto"
        db.Khos.InsertOnSubmit(nc)
        db.SubmitChanges()
    End Sub

    Public Shared Sub CapNhat(ByVal sach As Sach)
        Dim db As New ThuVienDataContext()
        Dim sch = (From s In db.Saches Where s.MS = sach.MS Select s).Single

        sch.Tua_sach = sach.Tua_sach
        sch.Tac_gia = sach.Tac_gia
        sch.MTL = sach.MTL
        sch.Nha_xuat_ban = sach.Nha_xuat_ban
        sch.Nam_xuat_ban = sach.Nam_xuat_ban

        db.SubmitChanges()
    End Sub

    Public Shared Sub Xoa(ByVal m As Integer)
        Dim db As New ThuVienDataContext
        Dim s = (From t In db.Saches Where t.MS = m Select t).Single
        db.Saches.DeleteOnSubmit(s)
    End Sub
End Class
